package com.dy.yunying.biz.dao.doris.impl;

import com.dy.yunying.api.datacenter.dto.AdDataDto;
import com.dy.yunying.api.datacenter.vo.AdDataVo;
import com.dy.yunying.api.enums.DataReportEnum;
import com.dy.yunying.biz.config.YunYingDorisTableProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.math.BigDecimal;
import java.util.*;

/**
 * @ClassName AdDataDorisDao
 * @Description todo
 * @Author nieml
 * @Time 2023/1/11 11:44
 * @Version 1.0
 **/
@Slf4j
@Component(value = "adDataDorisDao")
public class AdDataDorisDao {

	@Resource(name = "dataLayerDorisTemplate")
	private JdbcTemplate dorisTemplate;

	@Resource
	private YunYingDorisTableProperties yunYingDorisTableProperties;

	@Resource
	private DataLayerOfflineRepairDao dataLayerOfflineRepairDao;

	final private String indentStr = "		";
	final private String indentStr1 = "		";
	final private String indentStr2 = "				";
	final private String indentStr3 = "						";

	/**
	 * 求总数
	 *
	 * @param req
	 * @return
	 */
	public Long countDataTotal(AdDataDto req) {

		StringBuilder countSql = new StringBuilder();
		StringBuilder sql = this.getResultSql(req);
		countSql.append("SELECT COUNT(1) FROM (").append(sql).append(") res ");
		return dorisTemplate.queryForObject(countSql.toString(), Long.class);
	}

	public List<AdDataVo> selectAdDataSource(AdDataDto req) {

		final StringBuilder sql = this.getResultSql(req);

		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    period DESC\n");
		}
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}

		log.info("广告数据报表分页查询SQL: [\n{}]", sql.toString());

		List<AdDataVo> data = new ArrayList<>();

		long start = System.currentTimeMillis();
		SqlRowSet rs = dorisTemplate.queryForRowSet(sql.toString());
		long end = System.currentTimeMillis();
		log.info("广告数据报表分页查询耗时: {}ms", end - start);

		List<String> columns = Arrays.asList(rs.getMetaData().getColumnNames());

		//遍历 ResultSet
		while (rs.next()) {
			//组装成对象 添加到结果集
			AdDataVo item = ObjByRow(rs, columns);
			data.add(item);
		}

		return data;
	}

	private StringBuilder getResultSql(AdDataDto req) {
		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder beforeSql = getSql(req);

		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT \n");
		sql.append("    ").append(periodSql).append(" AS period ").append(selectDimFieldsSql).append(req.getShowRatio()).append(" AS showRatio,");
		sql.append("       round(rudeCost, 2) rudeCost, -- 原始消耗\n" +
				"    round(cost, 2) cost, -- 返点后消耗\n" +
				"    COALESCE(uuidnums, 0) deviceCount, -- 新增设备数\n" +
				"    COALESCE(usrnamenums, 0) usrnamenums, -- 新增设备注册数\n" +
				"    newUserCount, -- 新增用户数\n" +
				"    createRoleCount, -- 新增注册创角数\n" +
				"    certifiedCount, -- 新增注册实名数\n" +
				"    notCertifiedCount, -- 注册未实名数\n" +
				"    COALESCE(retention2, 0) num1, -- 次留人数\n" +
				"    COALESCE(usrpaynamenums, 0) newPayCount, -- 新增付费设备数\n" +
				"    if( showRatio = 1, round(newdevicesharfee,2), round(newdevicefees,2) ) newPayAmount, -- 新增充值实付金额\n" +
				"    if(showRatio = 1, round(newdeviceshargivemoney,2) ,  round(newdevicegivemoney,2)) newPayGivemoney, -- 新增充值代金券金额\n" +
				"    COALESCE(weektotalfeenums, 0) weekPayCount, -- 当周付费设备数\n" +
				"    if(showRatio = 1, round(weeksharfee,2), round(weektotalfee,2)) weektotalfee, -- 当周充值实付金额\n" +
				"    if(showRatio = 1, round(weekshargivemoney,2), round(weektotalgivemoney,2)) weekPayGivemoney, -- 当周充值代金券金额\n" +
				"    COALESCE(monthtotalfeenums, 0) monthPayCount, -- 当月付费设备数\n" +
				"    if(showRatio = 1, round(monthsharfee,2), round(monthtotalfee,2)) monthtotalfee, -- 当月充值实付金额\n" +
				"    if(showRatio = 1, round(monthshargivemoney,2), round(monthtotalgivemoney,2)) monthPayGivemoney, -- 当月充值代金券金额\n" +
				"    periodPayCount, -- 期内付费设备数\n" +
				"    IF(showRatio = 1,round(periodPayFee2,2), round(periodPayFee1,2)) AS periodPayFee, -- 期内充值实付金额\n" +
				"    IF(showRatio = 1, round(periodPayGivemoney2,2), round(periodPayGivemoney1,2)) AS periodPayGivemoney, -- 期内充值代金券金额\n" +
				"    COALESCE(totalPayfeenums, 0) totalPay, -- 累计付费设备数\n" +
				"    if(showRatio = 1, round(totalPaysharfee,2), round(totalPayfee,2)) totalPayfee, -- 累计充值实付金额\n" +
				"    if(showRatio = 1, round(totalPayshargivemoney,2), round(totalPaygivemoney,2)) totalPayGivemoney, -- 累计充值代金券金额\n" +
				"    COALESCE(activedevices, 0) activedevices, -- 活跃设备数\n" +
				"    COALESCE(activepaydevices, 0) activePayCount, -- 活跃付费设备数\n" +
				"    if(showRatio = 1, round(activesharfee,2), round(activetotalfee,2)) activetotalfee, -- 活跃充值实付金额\n" +
				"    if(showRatio = 1, round(activeshargivemoney,2), round(activetotalgivemoney,2)) activePayGivemoney, -- 活跃充值代金券金额\n" +
				"    round(if((usrnamenums) > 0, divide(cost, usrnamenums), 0), 2) deviceCose, -- 设备成本\n" +
				"    round(IF((usrnamenums) > 0, divide(createRoleCount * 100.00, usrnamenums), 0), 2) AS createRoleRate, -- 新增注册创角率\n" +
				"    round(IF((newUserCount) > 0, divide(certifiedCount * 100.00, newUserCount), 0), 2) AS certifiedRate, -- 新增实名制转化率\n" +
				"    round(if((usrnamenums) > 0, divide(retention2 * 100.00, usrnamenums), 0), 2) retention2Ratio, -- 次留\n" +
				"    round(if((usrnamenums) > 0, divide(usrpaynamenums * 100, usrnamenums), 0), 2) regPayRatio, -- 新增付费率\n" +
				"    round(IF((usrnamenums) > 0, divide(periodPayCount * 100.00, usrnamenums), 0), 2) AS periodPayRate, -- 期内付费率\n" +
				"    round(IF((activedevices) > 0, divide(activepaydevices * 100.00, activedevices), 0), 2) AS activePayRate, -- 活跃付费率\n" +
				"    IF(showRatio = 1, round(if((usrnamenums) > 0, divide((newdevicesharfee), usrnamenums), 0), 2), round(if((usrnamenums) > 0, divide((newdevicefees), usrnamenums), 0), 2)) regarpu, -- 新增注册ARPU\n" +
				"    IF(showRatio = 1, round(IF((usrpaynamenums) > 0, divide((newdevicesharfee), usrpaynamenums), 0), 2),round(IF((usrpaynamenums) > 0, divide((newdevicefees), usrpaynamenums), 0), 2)) AS newPayArppu, -- 新增付费ARPPU\n" +
				"    IF(showRatio = 1, round(if((activedevices) > 0, divide((activesharfee), activedevices), 0), 2), round(if((activedevices) > 0, divide((activetotalfee), activedevices), 0), 2)) actarpu, -- 活跃设备ARPU\n" +
				"    IF(showRatio = 1, round(IF((activepaydevices) > 0, divide((activesharfee), activepaydevices), 0), 2), round(IF((activepaydevices) > 0, divide((activetotalfee), activepaydevices), 0), 2)) activePayArppu, -- 活跃付费ARPPU\n" +
				"    IF(showRatio = 1, round(if(cost > 0, divide(newdevicesharfee * 100.00, cost), 0), 2), round(if(cost > 0, divide(newdevicefees * 100.00, cost), 0), 2)) roi1, -- 首日ROI\n" +
				"    IF(showRatio = 1, round(if(cost > 0, divide(weeksharfee * 100.00, cost), 0), 2), round(if(cost > 0, divide(weektotalfee * 100.00, cost), 0), 2)) weekRoi, -- 当周ROI\n" +
				"    IF(showRatio = 1, round(if(cost > 0, divide(monthsharfee * 100.00, cost), 0), 2), round(if(cost > 0, divide(monthtotalfee * 100.00, cost), 0), 2)) monthRoi, -- 当月ROI\n" +
				"    IF(showRatio = 1, round(if(cost > 0, divide(totalPaysharfee * 100.00, cost), 0), 2), round(if(cost > 0, divide(totalPayfee * 100.00, cost), 0), 2)) allRoi -- 累计ROI\n");
		sql.append("FROM\n");
		sql.append(" (\n");
		sql.append(beforeSql);
		sql.append("  ) res \n");
		return sql;
	}

	private StringBuilder getSql(AdDataDto req) {
		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder groupByFieldsSql = getGroupByFieldsSql(req);

		StringBuilder arSql = getArSql(req);
		StringBuilder actSql = getActSql(req);
		StringBuilder costSql = getCostSql(req);

		StringBuilder fullJoinSelectFieldsSql = getFullJoinSelectFieldsSql(req, "ar", "act");
		StringBuilder fullJoinOnFieldsSql = getFullJoinOnFieldsSql(req, "ar", "act");
		StringBuilder fullJoinSelectFieldsSql2 = getFullJoinSelectFieldsSql(req, "arres", "costres");
		StringBuilder fullJoinOnFieldsSql2 = getFullJoinOnFieldsSql(req, "arres", "costres");

		StringBuilder sql = new StringBuilder();
		//所有指标full join
		//sql.append(" ( ").append("\n");
		sql.append(" select ").append("\n");
		sql.append(periodSql);
		sql.append(selectDimFieldsSql);
		sql.append(req.getShowRatio()).append(" as showRatio,").append("\n");
		sql.append("    SUM(usrnamenums) usrnamenums,-- 新增设备注册数 ").append("\n");
		sql.append("    SUM(uuidnums) uuidnums, -- 新增设备 ").append("\n");
		sql.append("    SUM(retention2) retention2, -- 次留 ").append("\n");
		sql.append("    SUM(usrpaynamenums) usrpaynamenums, -- 新增设备付费数 ").append("\n");
		sql.append("    SUM(newdevicefees) newdevicefees, -- 新增充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(newdevicesharfee) newdevicesharfee, -- 新增充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(newdevicegivemoney) newdevicegivemoney, -- 新增充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(newdeviceshargivemoney) newdeviceshargivemoney, -- 新增充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(weektotalfeenums) weektotalfeenums, -- 当周充值人数 ").append("\n");
		sql.append("    SUM(weektotalfee) weektotalfee, -- 当周充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(weeksharfee) weeksharfee, -- 当周充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(weektotalgivemoney) weektotalgivemoney, -- 当周充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(weekshargivemoney) weekshargivemoney, -- 当周充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(monthtotalfeenums) monthtotalfeenums, -- 当月充值人数 ").append("\n");
		sql.append("    SUM(monthtotalfee) monthtotalfee, -- 当月充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(monthsharfee) monthsharfee, -- 当月充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(monthtotalgivemoney) monthtotalgivemoney, -- 当月充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(monthshargivemoney) monthshargivemoney, -- 当月充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(totalPayfeenums) totalPayfeenums, -- 累计充值人数 ").append("\n");
		sql.append("    SUM(totalPayfee) totalPayfee, -- 累计充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(totalPaysharfee) totalPaysharfee, -- 累计充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(totalPaygivemoney) totalPaygivemoney, -- 累计充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(totalPayshargivemoney) totalPayshargivemoney, -- 累计充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(newUserCount) AS newUserCount, -- 新增用户数 ").append("\n");
		sql.append("    SUM(createRoleCount) AS createRoleCount, -- 新增注册创角数 ").append("\n");
		sql.append("    SUM(certifiedCount) AS certifiedCount, -- 新增注册实名数 ").append("\n");
		sql.append("    SUM(notCertifiedCount) AS notCertifiedCount, -- 注册未实名数 ").append("\n");
		sql.append("    SUM(periodPayCount) periodPayCount, -- 期内设备付费数 ").append("\n");
		sql.append("    SUM(periodPayFee1) periodPayFee1, -- 期内充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(periodPayFee2) periodPayFee2, -- 期内充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(periodPayGivemoney1) periodPayGivemoney1, -- 期内充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(periodPayGivemoney2) periodPayGivemoney2, -- 期内充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(activedevices) activedevices, -- 活跃设备数 ").append("\n");
		sql.append("    SUM(activepaydevices) activepaydevices, -- 活跃付费设备数 ").append("\n");
		sql.append("    SUM(activetotalfee) activetotalfee, -- 活跃充值实付金额（分成前） ").append("\n");
		sql.append("    SUM(activesharfee) activesharfee, -- 活跃充值实付金额（分成后） ").append("\n");
		sql.append("    SUM(activetotalgivemoney) activetotalgivemoney, -- 活跃充值代金券金额（分成前） ").append("\n");
		sql.append("    SUM(activeshargivemoney) activeshargivemoney, -- 活跃充值代金券金额（分成后） ").append("\n");
		sql.append("    SUM(rudeCost) rudeCost, -- 原始消耗 ").append("\n");
		sql.append("    SUM(cost) cost -- 返点后消耗 ").append("\n");
		sql.append(" FROM ").append("\n");
		sql.append(indentStr).append(" ( ").append("\n");
		sql.append(indentStr).append(" select ").append("\n");
		sql.append(indentStr).append(fullJoinSelectFieldsSql2).append("\n");
		sql.append(indentStr).append(" COALESCE(usrnamenums,0) usrnamenums, -- 新增设备注册数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(uuidnums,0) uuidnums, -- 新增设备 ").append("\n");
		sql.append(indentStr).append(" COALESCE(retention2,0) retention2, -- 次留 ").append("\n");
		sql.append(indentStr).append(" COALESCE(usrpaynamenums,0) usrpaynamenums, -- 新增设备付费数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(newdevicefees,0) newdevicefees, -- 新增充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(newdevicesharfee,0) newdevicesharfee, -- 新增充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(newdevicegivemoney,0) newdevicegivemoney, -- 新增充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(newdeviceshargivemoney,0) newdeviceshargivemoney, -- 新增充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(weektotalfeenums,0) weektotalfeenums, -- 当周充值人数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(weektotalfee,0) weektotalfee, -- 当周充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(weeksharfee,0) weeksharfee, -- 当周充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(weektotalgivemoney,0) weektotalgivemoney, -- 当周充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(weekshargivemoney,0) weekshargivemoney, -- 当周充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthtotalfeenums,0) monthtotalfeenums, -- 当月充值人数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthtotalfee,0) monthtotalfee, -- 当月充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthsharfee,0) monthsharfee, -- 当月充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthtotalgivemoney,0) monthtotalgivemoney, -- 当月充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(monthshargivemoney,0) monthshargivemoney, -- 当月充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPayfeenums,0) totalPayfeenums, -- 累计充值人数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPayfee,0) totalPayfee, -- 累计充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPaysharfee,0) totalPaysharfee, -- 累计充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPaygivemoney,0) totalPaygivemoney, -- 累计充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(totalPayshargivemoney,0) totalPayshargivemoney, -- 累计充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(createRoleCount,0) createRoleCount, -- 新增注册创角数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(newUserCount,0) newUserCount,  -- 新增用户数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(certifiedCount,0) certifiedCount, -- 新增注册实名数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(notCertifiedCount,0) notCertifiedCount, -- 注册未实名数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(rudeCost,0) rudeCost, -- 原始消耗 ").append("\n");
		sql.append(indentStr).append(" COALESCE(cost,0) cost, -- 返点后消耗 ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayCount,0) periodPayCount, -- 期内设备付费数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayFee1,0) periodPayFee1, -- 期内充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayFee2,0) periodPayFee2, -- 期内充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayGivemoney1,0) periodPayGivemoney1, -- 期内充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(periodPayGivemoney2,0) periodPayGivemoney2, -- 期内充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(activedevices,0) activedevices, -- 活跃设备数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(activepaydevices,0) activepaydevices, -- 活跃付费设备数 ").append("\n");
		sql.append(indentStr).append(" COALESCE(activetotalfee,0) activetotalfee,-- 活跃充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(activesharfee,0) activesharfee, -- 活跃充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" COALESCE(activetotalgivemoney,0) activetotalgivemoney, -- 活跃充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" COALESCE(activeshargivemoney,0) activeshargivemoney -- 活跃充值代金券金额（分成后） ").append("\n");
		sql.append(" from ").append("\n");
		sql.append(indentStr).append(" ( ").append("\n");
		sql.append(indentStr).append(" select ").append("\n");
		sql.append(indentStr).append(fullJoinSelectFieldsSql).append("\n");
		sql.append(indentStr).append(" usrnamenums,-- 新增设备注册数  ").append("\n");
		sql.append(indentStr).append(" uuidnums, -- 新增设备  ").append("\n");
		sql.append(indentStr).append(" retention2, -- 次留  ").append("\n");
		sql.append(indentStr).append(" usrpaynamenums, -- 新增设备付费数  ").append("\n");
		sql.append(indentStr).append(" newdevicefees, -- 新增充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" newdevicesharfee, -- 新增充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" newdevicegivemoney, -- 新增充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" newdeviceshargivemoney, -- 新增充值代金券金额（分成后）  ").append("\n");
		sql.append(indentStr).append(" weektotalfeenums, -- 当周充值人数  ").append("\n");
		sql.append(indentStr).append(" weektotalfee, -- 当周充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" weeksharfee, -- 当周充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" weektotalgivemoney, -- 当周充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" weekshargivemoney, -- 当周充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" monthtotalfeenums, -- 当月充值人数 ").append("\n");
		sql.append(indentStr).append(" monthtotalfee, -- 当月充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" monthsharfee, -- 当月充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" monthtotalgivemoney, -- 当月充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" monthshargivemoney, -- 当月充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" totalPayfeenums, -- 累计充值人数 ").append("\n");
		sql.append(indentStr).append(" totalPayfee, -- 累计充值实付金额（分成前）  ").append("\n");
		sql.append(indentStr).append(" totalPaysharfee, -- 累计充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" totalPaygivemoney, -- 累计充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" totalPayshargivemoney, -- 累计充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" newUserCount, -- 新增用户数  ").append("\n");
		sql.append(indentStr).append(" createRoleCount, -- 新增注册创角数  ").append("\n");
		sql.append(indentStr).append(" certifiedCount, -- 新增注册实名数  ").append("\n");
		sql.append(indentStr).append(" (newUserCount - certifiedCount ) as notCertifiedCount, -- 注册未实名数  ").append("\n");
		sql.append(indentStr).append(" activedevices, -- 活跃设备数  ").append("\n");
		sql.append(indentStr).append(" activepaydevices, -- 活跃付费设备数  ").append("\n");
		sql.append(indentStr).append(" activetotalfee,-- 活跃充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" activesharfee, -- 活跃充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" activetotalgivemoney, -- 活跃充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" activeshargivemoney, -- 活跃充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" periodPayCount, -- 期内设备付费数  ").append("\n");
		sql.append(indentStr).append(" periodPayFee1, -- 期内充值实付金额（分成前） ").append("\n");
		sql.append(indentStr).append(" periodPayFee2, -- 期内充值实付金额（分成后） ").append("\n");
		sql.append(indentStr).append(" periodPayGivemoney1, -- 期内充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr).append(" periodPayGivemoney2 -- 期内充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr).append(" from ").append("\n");
		sql.append(arSql);
		sql.append(indentStr).append(" full join ").append("\n");
		sql.append(actSql);
		sql.append(indentStr).append(" on ").append(fullJoinOnFieldsSql).append("\n");
		sql.append(indentStr).append(" ) arres ").append("\n");
		sql.append(indentStr).append(" full join ").append("\n");
		sql.append(costSql);
		sql.append(indentStr).append(" on ").append(fullJoinOnFieldsSql2).append("\n");
		sql.append(" ) res ").append("\n");
		sql.append(" group by ").append("\n");
		sql.append(periodSql);
		sql.append(groupByFieldsSql).append("\n");
		return sql;
	}

	/**
	 *  指标子查询sql：新增、付费、期内、角色账号
	 * */
	private StringBuilder getArSql(AdDataDto req){
		Long rsTime = req.getSTime();
		Long reTime = req.getETime();
		Long startPayDate = req.getStartPayDate();
		Long endPayDate = req.getEndPayDate();

		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder whereFieldsSql = getWhereFieldsSql(req);
		StringBuilder groupByFieldsSql = getGroupByFieldsSql(req);
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr1).append(" ( ").append("\n");
		sql.append(indentStr1).append(" select ").append("\n");
		//日期字段+维度字段
		sql.append(indentStr1).append(periodSql).append(selectDimFieldsSql);
		//指标字段
		sql.append(indentStr1).append(" bitmap_union_count(reg_device_bitmap) usrnamenums,-- 新增设备注册数 ").append("\n");
		sql.append(indentStr1).append(" bitmap_union_count(new_device_bitmap) uuidnums, -- 新增设备数 ").append("\n");
		sql.append(indentStr1).append(" bitmap_union_count(active_device_bitmap_1) retention2, -- 次留 ").append("\n");
		sql.append(indentStr1).append(" bitmap_union_count(new_device_pay_bitmap) usrpaynamenums, -- 新增设备付费数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(sum(new_fee_1),0) newdevicefees, -- 新增充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(sum(new_fee_1*sharing),0) newdevicesharfee, -- 新增充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(sum(new_givemoney_1),0) newdevicegivemoney, -- 新增充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(sum(new_givemoney_1*sharing),0) newdeviceshargivemoney, -- 新增充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" 0 as weektotalfeenums, -- 当周充值人数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_fee_week),0) weektotalfee, -- 当周充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_fee_week * sharing), 0) weeksharfee, -- 当周充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_givemoney_week),0) weektotalgivemoney, -- 当周充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_givemoney_week * sharing), 0) weekshargivemoney, -- 当周充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" 0 as monthtotalfeenums, -- 当月充值人数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_fee_month), 0) monthtotalfee, -- 当月充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_fee_month * sharing), 0) monthsharfee, -- 当月充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_givemoney_month), 0) monthtotalgivemoney, -- 当月充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(new_givemoney_month * sharing), 0) monthshargivemoney, -- 当月充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(active_device_pay_bitmap), 0) totalPayfeenums, -- 累计充值人数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(total_fee), 0) totalPayfee, -- 累计充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(total_fee * sharing), 0) totalPaysharfee, -- 累计充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(total_givemoney), 0) totalPaygivemoney, -- 累计充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(total_givemoney * sharing), 0) totalPayshargivemoney, -- 累计充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(create_role_num), 0) AS createRoleCount, -- 新增注册创角数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(reg_account_bitmap), 0) AS newUserCount, -- 新增用户数 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count(certi_account_bitmap), 0) AS certifiedCount, -- 新增注册实名数  ").append("\n");
		sql.append(indentStr1).append(" COALESCE(bitmap_union_count( if( ").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_device_pay_bitmap,bitmap_empty() ) ), 0) periodPayCount, -- 期内设备付费数 ").append("\n");
		sql.append(indentStr1).append(" SUM( if(").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_fee,0) ) periodPayFee1, -- 期内充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" SUM( if(").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_fee * sharing,0) ) periodPayFee2, -- 期内充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" SUM( if(").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_givemoney,0) ) periodPayGivemoney1, -- 期内充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" SUM( if(").append(" active_day between to_date(").append(startPayDate).append(")").append(" and ").append(" to_date(").append(endPayDate).append(")").append(",active_givemoney * sharing,0) ) periodPayGivemoney2 -- 期内充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" from ").append("\n");
		sql.append(indentStr2).append(" ( ").append("\n");
		sql.append(indentStr2).append(" select ").append("\n");
		sql.append(indentStr2).append(" ar.reg_day as day,ar.active_day,days_diff(ar.active_day,ar.reg_day) as daysDiff,ar.reg_week as week,ar.reg_month as month,reg_year as year,'汇总' as collect,ar.media_code,ar.chl_app as appchl,ar.ad_id as adid, ").append("\n");
		sql.append(indentStr2).append(" dimchl.sharing as sharing,dimchl.chl_main as parentchl,dimchl.game_sub as gameid,dimchl.game_main as pgid,dimchl.game_sub_name,dimchl.game_main_name,dimchl.os as os, ").append("\n");
		sql.append(indentStr2).append(" dimchl.investor as investor,dimchl.investor_name as investorName,dimchl.dept_id as deptId,dimchl.dept_name as deptName,dimchl.dept_group_id as userGroupId,dimchl.dept_group_name as userGroupName, ").append("\n");
		sql.append(indentStr2).append(" dimchl.spread_type spread_type, ").append("\n");
		sql.append(indentStr2).append(" reg_device_bitmap, -- 新增设备注册数、付费注册数（二次计算） ").append("\n");
		sql.append(indentStr2).append(" new_device_bitmap, -- 新增设备 ").append("\n");
		sql.append(indentStr2).append(" active_device_bitmap_1, -- 次留、付费留存数（二次计算） ").append("\n");
		sql.append(indentStr2).append(" new_device_pay_bitmap, -- 新增设备付费数、付费留存数（二次计算）、付费注册数（二次计算） ").append("\n");
		sql.append(indentStr2).append(" new_fee_1, -- 新增充值实付金额（分成前）新增充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_givemoney_1, -- 新增充值代金券金额（分成前） / 新增充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_fee_week, -- 当周充值实付金额（分成前）/ 当周充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_givemoney_week, -- 当周充值代金券金额（分成前）/ 当周充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_fee_month, -- 当月充值实付金额（分成前）、 当月充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" new_givemoney_month,-- 当月充值代金券金额（分成前）/ 当月充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" active_device_pay_bitmap, -- 累计充值人数/期内设备付费数/活跃付费设备数 ").append("\n");
		sql.append(indentStr2).append(" total_fee, -- 累计充值实付金额（分成前）、累计充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" total_givemoney, -- 累计充值代金券金额（分成前）、累计充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" create_role_num , -- 新增注册创角数 ").append("\n");
		sql.append(indentStr2).append(" certi_device_bitmap,-- 新增实名设备数 ").append("\n");
		sql.append(indentStr2).append(" reg_account_bitmap,-- 新增设备注册账号数 ").append("\n");
		sql.append(indentStr2).append(" certi_account_bitmap,-- 新增注册实名账号数 ").append("\n");
		sql.append(indentStr2).append(" active_fee, -- 期内充值实付金额（分成前）/ 期内充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" active_givemoney -- 期内充值代金券金额（分成前）/ 期内充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" from ").append("\n");
//		sql.append(indentStr3).append(" ( ").append("\n");
//		sql.append(indentStr3).append(" select ").append("\n");
//		sql.append(indentStr3).append(" reg_day,active_day,days_diff(active_day,reg_day) daysDiff,reg_week,reg_month,reg_year,media_code,chl_app,ad_id,creative_id, ").append("\n");
//		sql.append(indentStr3).append(" reg_device_bitmap, -- 新增设备注册数、付费注册数（二次计算） ").append("\n");
//		sql.append(indentStr3).append(" new_device_bitmap, -- 新增设备 ").append("\n");
//		sql.append(indentStr3).append(" active_device_bitmap_1, -- 次留、付费留存数（二次计算） ").append("\n");
//		sql.append(indentStr3).append(" new_device_pay_bitmap, -- 新增设备付费数、付费留存数（二次计算）、付费注册数（二次计算） ").append("\n");
//		sql.append(indentStr3).append(" new_fee_1, -- 新增充值实付金额（分成前）新增充值实付金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" new_givemoney_1, -- 新增充值代金券金额（分成前） / 新增充值代金券金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" new_fee_week, -- 当周充值实付金额（分成前）/ 当周充值实付金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" new_givemoney_week, -- 当周充值代金券金额（分成前）/ 当周充值代金券金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" new_fee_month, -- 当月充值实付金额（分成前）、 当月充值实付金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" new_givemoney_month,-- 当月充值代金券金额（分成前）/ 当月充值代金券金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" active_device_pay_bitmap, -- 累计充值人数 ").append("\n");
//		sql.append(indentStr3).append(" total_fee, -- 累计充值实付金额（分成前）、累计充值实付金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" total_givemoney, -- 累计充值代金券金额（分成前）、累计充值代金券金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" create_role_num , -- 新增注册创角数 ").append("\n");
//		sql.append(indentStr3).append(" certi_device_bitmap,-- 新增实名设备数 ").append("\n");
//		sql.append(indentStr3).append(" reg_account_bitmap,-- 新增设备注册账号数  ").append("\n");
//		sql.append(indentStr3).append(" certi_account_bitmap,-- 新增注册实名账号数 ").append("\n");
//		sql.append(indentStr3).append(" active_fee, -- 期内充值实付金额（分成前）/ 期内充值实付金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" active_givemoney, -- 期内充值代金券金额（分成前）/ 期内充值代金券金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" active_device_bitmap, -- 活跃设备数 ").append("\n");
//		sql.append(indentStr3).append(" active_pay_num -- 活跃付费次数 ").append("\n");
//		sql.append(indentStr3).append(" from ");
		sql.append(indentStr3).append(dataLayerOfflineRepairDao.getAdsOperation3399AdReportSql(rsTime,reTime,yunYingDorisTableProperties.getREG_DAY(), DataReportEnum.ADDATA.getType())).append("\n");
//		sql.append(indentStr3).append(" ) ar ").append("\n");
		sql.append(indentStr3).append(" left join ").append("\n");
		sql.append(indentStr3).append(yunYingDorisTableProperties.getVDimOperation3399ChannelGameInvestor()).append(" dimchl ").append("\n");
		sql.append(indentStr3).append(" on ar.chl_app = dimchl.chl_app ").append("\n");
		sql.append(indentStr2).append(" ) ar  ").append("\n");
		sql.append(indentStr2).append(" where 1=1  ").append("\n");
		//过滤字段
		sql.append(indentStr2).append(whereFieldsSql).append("\n");
		sql.append(indentStr2).append(" group by ").append("\n");
		//日期分组字段+维度分组字段
		sql.append(indentStr2).append(periodSql).append(groupByFieldsSql).append("\n");
		sql.append(indentStr1).append(" ) ar ").append("\n");
		return sql;
	}

	/**
	 * 活跃指标
	 * */
	private StringBuilder getActSql(AdDataDto req){
		Long rsTime = req.getSTime();
		Long reTime = req.getETime();

		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder whereFieldsSql = getWhereFieldsSql(req);
		StringBuilder groupByFieldsSql = getGroupByFieldsSql(req);
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr1).append(" ( ").append("\n");
		sql.append(indentStr1).append(" select ").append("\n");
		//日期字段+维度字段
		sql.append(indentStr1).append(periodSql).append(selectDimFieldsSql);
		//指标字段
		sql.append(indentStr1).append(" bitmap_union_count(active_device_bitmap) activedevices, -- 活跃设备数 ").append("\n");
		sql.append(indentStr1).append(" bitmap_union_count(active_device_pay_bitmap) activepaydevices, -- 活跃付费设备数 ").append("\n");
		sql.append(indentStr1).append(" SUM(active_fee) activetotalfee, -- 活跃充值实付金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" SUM(active_fee * sharing) activesharfee, -- 活跃充值实付金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" SUM(active_givemoney) activetotalgivemoney, -- 活跃充值代金券金额（分成前） ").append("\n");
		sql.append(indentStr1).append(" SUM(active_givemoney * sharing) activeshargivemoney -- 活跃充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr1).append(" from ").append("\n");
		sql.append(indentStr2).append(" ( ").append("\n");
		sql.append(indentStr2).append(" select ").append("\n");
		sql.append(indentStr2).append(" ar.active_day as day,ar.week as week,ar.month as month,ar.year as year,'汇总' as collect,ar.media_code,ar.chl_app as appchl,ar.ad_id as adid, ").append("\n");
		sql.append(indentStr2).append(" dimchl.sharing as sharing,dimchl.chl_main as parentchl,dimchl.game_sub as gameid,dimchl.game_main as pgid,dimchl.game_sub_name,dimchl.game_main_name,dimchl.os as os, ").append("\n");
		sql.append(indentStr2).append(" dimchl.investor as investor,dimchl.investor_name as investorName,dimchl.dept_id as deptId,dimchl.dept_name as deptName,dimchl.dept_group_id as userGroupId,dimchl.dept_group_name as userGroupName, ").append("\n");
		sql.append(indentStr2).append(" dimchl.spread_type spread_type, ").append("\n");
		sql.append(indentStr2).append(" active_device_bitmap, -- 活跃设备数 ").append("\n");
		sql.append(indentStr2).append(" active_device_pay_bitmap, -- 活跃付费设备数 ").append("\n");
		sql.append(indentStr2).append(" active_fee, -- 活跃充值实付金额（分成前）/ 活跃充值实付金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" active_givemoney, -- 活跃充值代金券金额（分成前）/ 活跃充值代金券金额（分成后） ").append("\n");
		sql.append(indentStr2).append(" active_pay_num -- 活跃付费次数 ").append("\n");
		sql.append(indentStr2).append(" from ").append("\n");
//		sql.append(indentStr3).append(" ( ").append("\n");
//		sql.append(indentStr3).append(" select ").append("\n");
//		sql.append(indentStr3).append(" active_day,week,month,year,reg_day,reg_week,reg_month,media_code,chl_app,ad_id,creative_id, ").append("\n");
//		sql.append(indentStr3).append(" active_device_bitmap, -- 活跃设备数 ").append("\n");
//		sql.append(indentStr3).append(" active_device_pay_bitmap, -- 活跃付费设备数 ").append("\n");
//		sql.append(indentStr3).append(" active_fee, -- 活跃充值实付金额（分成前）/ 活跃充值实付金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" active_givemoney, -- 活跃充值代金券金额（分成前）/ 活跃充值代金券金额（分成后） ").append("\n");
//		sql.append(indentStr3).append(" active_pay_num -- 活跃付费次数 ").append("\n");
//		sql.append(indentStr3).append(" from ");
		sql.append(indentStr3).append(dataLayerOfflineRepairDao.getAdsOperation3399AdReportSql(rsTime,reTime,yunYingDorisTableProperties.getACTIVE_DAY(), DataReportEnum.ADDATA.getType())).append("\n");
//		sql.append(indentStr3).append(" ) ar ").append("\n");
		sql.append(indentStr3).append(" left join ").append("\n");
		sql.append(indentStr3).append(yunYingDorisTableProperties.getVDimOperation3399ChannelGameInvestor()).append(" dimchl ").append("\n");
		sql.append(indentStr3).append(" on ar.chl_app = dimchl.chl_app ").append("\n");
		sql.append(indentStr2).append(" ) act  ").append("\n");
		sql.append(indentStr2).append(" where 1=1  ").append("\n");
		//过滤字段
		sql.append(indentStr2).append(whereFieldsSql).append("\n");
		sql.append(indentStr2).append(" group by ").append("\n");
		//日期分组字段+维度分组字段
		sql.append(indentStr2).append(periodSql).append(groupByFieldsSql).append("\n");
		sql.append(indentStr1).append(" ) act ").append("\n");
		return sql;
	}

	/**
	 *  指标子查询sql：消耗
	 * */
	private StringBuilder getCostSql(AdDataDto req){
		Long rsTime = req.getSTime();
		Long reTime = req.getETime();

		StringBuilder periodSql = getPeriodSql(req);
		StringBuilder selectDimFieldsSql = getSelectDimFieldsSql(req);
		StringBuilder whereFieldsSql = getWhereFieldsSql(req);
		StringBuilder groupByFieldsSql = getGroupByFieldsSql(req);
		StringBuilder sql = new StringBuilder();
		sql.append(indentStr1).append(" ( ").append("\n");
		sql.append(indentStr1).append(" select ").append("\n");
		//日期字段+维度字段
		sql.append(indentStr1).append(periodSql).append(selectDimFieldsSql);
		//指标字段
		sql.append(indentStr1).append(" COALESCE(SUM(cost), 0) rudeCost, -- 原始消耗 ").append("\n");
		sql.append(indentStr1).append(" COALESCE(SUM(round((cost*100)/(rebate+100) ,3)), 0) cost -- 返点后消耗 ").append("\n");
		sql.append(indentStr1).append(" from ").append("\n");
		sql.append(indentStr2).append(" ( ").append("\n");
		sql.append(indentStr2).append(" select ").append("\n");
		sql.append(indentStr2).append(" cost.day as day,cost.week as week,cost.month as month,cost.year as year,'汇总' as collect,cost.date_type,cost.media_code,cost.account_id as advertiserid,cost.ad_id as adid, ").append("\n");
		sql.append(indentStr2).append(" dimadchl.investor as investor,dimadchl.investor_name as investorName,dimadchl.dept_id as deptId,dimadchl.dept_name as deptName,dimadchl.dept_group_id as userGroupId,dimadchl.dept_group_name as userGroupName, ").append("\n");
		sql.append(indentStr2).append(" dimadchl.rebate,dimadchl.game_main as pgid,dimadchl.game_sub as gameid,dimadchl.chl_main as parentchl,dimadchl.chl_app as appchl,dimadchl.os as os, ").append("\n");
		sql.append(indentStr2).append(" dimadchl.convert_name as convertName,dimadchl.deep_convert as deepConvert,dimadchl.status status, dimadchl.spread_type spread_type,").append("\n");
		sql.append(indentStr2).append(" cost -- 消耗 ").append("\n");
		sql.append(indentStr2).append(" from ").append("\n");
		sql.append(indentStr3).append(" ( ").append("\n");
		sql.append(indentStr3).append(" select ").append("\n");
		sql.append(indentStr3).append(" day,month,week,year,date_type,media_code,account_id,campaign_id,ad_id,creative_id, ").append("\n");
		sql.append(indentStr3).append(" cost -- 消耗 ").append("\n");
		sql.append(indentStr3).append(" from ").append(yunYingDorisTableProperties.getDwsOperation3399AdCostD()).append("\n");
		//日期过滤
		sql.append(indentStr3).append(" where day BETWEEN ").append(" to_date(").append(rsTime).append(")").append(" and ")
				.append(" to_date(").append(reTime).append(")").append("\n");
		sql.append(indentStr3).append(" ) cost ").append("\n");
		sql.append(indentStr3).append(" left join ").append("\n");
		sql.append(indentStr3).append(yunYingDorisTableProperties.getVDimOperation3399AdChannel()).append(" dimadchl ").append("\n");
		sql.append(indentStr3).append(" on cost.ad_id = dimadchl.ad_id and cost.media_code = dimadchl.media_code ").append("\n");
		sql.append(indentStr2).append(" ) cost  ").append("\n");
		sql.append(indentStr2).append(" where 1=1  ").append("\n");
		//过滤字段
		sql.append(indentStr2).append(whereFieldsSql).append("\n");
		sql.append(indentStr2).append(" group by ").append("\n");
		//日期分组字段+维度分组字段
		sql.append(indentStr2).append(periodSql).append(groupByFieldsSql).append("\n");
		sql.append(indentStr1).append(" ) costres ").append("\n");
		return sql;
	}


	/**
	 * where Fields：注册时间、活跃时间、主渠道、主游戏、子游戏、部门、投放人
	 * */
	private StringBuilder getWhereFieldsSql(AdDataDto req) {
		final String parentchlArr = req.getParentchlArr();
		final String pgidArr = req.getPgidArr();
		final String gameidArr = req.getGameidArr();
		final String deptIdArr = req.getDeptIdArr();
		final String investorArr = req.getInvestorArr();

		StringBuilder sql = new StringBuilder();
		sql.append(" and spread_type <> 2 ");
		if (StringUtils.isNotBlank(pgidArr)) {
			sql.append(" AND pgid IN (").append(pgidArr).append(")");
		}
		if (StringUtils.isNotBlank(gameidArr)) {
			sql.append(" AND gameid IN (").append(gameidArr).append(")");
		}
		if (StringUtils.isNotBlank(parentchlArr)) {
			sql.append(" AND parentchl IN ('").append(parentchlArr.replaceAll(",", "', '")).append("')");
		}

		if (StringUtils.isNotBlank(deptIdArr)) {
			sql.append(" AND deptId IN (").append(deptIdArr).append(")");
		}
		if (StringUtils.isNotBlank(investorArr)) {
			sql.append(" AND investor IN (").append(investorArr).append(")");
		}

		// 账号渠道权限
		if (req.getIsSys() != 1) {
			sql.append(" AND investor IN (").append(req.getUserIds()).append(")");
		}
		return sql;
	}

	/**
	 * groupby Fields
	 * 渠道、转化目标、广告账户、广告计划、主游戏、子游戏、分包、系统、部门、投放人、组别
	 * */
	private StringBuilder getGroupByFieldsSql(AdDataDto req) {
		StringBuilder groupColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		if (StringUtils.isNotBlank(queryColumn)) {
			groupColumnSql.append(",");
			groupColumnSql.append(queryColumn);
			if (queryColumn.contains("deptId")) {
				groupColumnSql.append(", deptName");
			}
			if (queryColumn.contains("investor")) {
				groupColumnSql.append(", investorName");
			}
			if (queryColumn.contains("userGroupId")) {
				groupColumnSql.append(", userGroupName");
			}
			if (queryColumn.contains("convertName")) {
				groupColumnSql.append(", deepConvert");
			}
		}
		return groupColumnSql;
	}

	/**
	 * select dim Fields
	 * */
	private StringBuilder getSelectDimFieldsSql(AdDataDto req) {
		StringBuilder queryColumnSql = new StringBuilder();
		//无论是否有分组项，必定有一个","
		queryColumnSql.append(",");
		String queryColumn = req.getQueryColumn();
		if (StringUtils.isNotBlank(queryColumn)) {
			queryColumnSql.append(queryColumn).append(",");
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("deptName, ");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("investorName, ");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("userGroupName, ");
			}
			queryColumnSql.append("\n");
		}
		return queryColumnSql;
	}

	/**
	 *  select Period field
	 * */
	private StringBuilder getPeriodSql(AdDataDto req) {
		String period = req.getPeriod();
		StringBuilder sql = new StringBuilder();
		sql.append(period);
		return sql;
	}

	/**
	 *  full join select fields
	 * */
	private StringBuilder getFullJoinSelectFieldsSql(AdDataDto req, String left, String right){
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();

		queryColumnSql.append("COALESCE(").append(left).append(".").append(this.getPeriodSql(req)).append(",").append(right).append(".").append(this.getPeriodSql(req)).append(") ").append(this.getPeriodSql(req)).append(",");
		if (StringUtils.isNotBlank(queryColumn)) {
			if (queryColumn.contains("pgid")) {
				queryColumnSql.append("COALESCE(").append(left).append(".pgid,").append(right).append(".pgid) pgid,");
			}
			if (queryColumn.contains("os")) {
				queryColumnSql.append("COALESCE(").append(left).append(".os,").append(right).append(".os) os,");
			}
			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("COALESCE(").append(left).append(".deptId,").append(right).append(".deptId) deptId,COALESCE(").append(left).append(".deptName,").append(right).append(".deptName) deptName,");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("COALESCE(").append(left).append(".investor,").append(right).append(".investor) investor,COALESCE(").append(left).append(".investorName,").append(right).append(".investorName) investorName,");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("COALESCE(").append(left).append(".userGroupId,").append(right).append(".userGroupId) userGroupId,COALESCE(").append(left).append(".userGroupName,").append(right).append(".userGroupName) userGroupName,");
			}
		}
		sql.append(queryColumnSql);
		return sql;
	}

	/**
	 * full join  on fields
	 * */
	private StringBuilder getFullJoinOnFieldsSql(AdDataDto req, String left, String right){
		StringBuilder sql = new StringBuilder();
		StringBuilder queryColumnSql = new StringBuilder();
		String queryColumn = req.getQueryColumn();
		//日期
		queryColumnSql.append(left).append(".").append(this.getPeriodSql(req)).append(" = ").append(right).append(".").append(this.getPeriodSql(req)).append(" ");
		if (StringUtils.isNotBlank(queryColumn)) {
			
			if (queryColumn.contains("pgid")) {
				queryColumnSql.append("AND ").append(left).append(".pgid = ").append(right).append(".pgid ");
			}
			if (queryColumn.contains("os")) {
				queryColumnSql.append("AND ").append(left).append(".os = ").append(right).append(".os ");
			}

			if (queryColumn.contains("deptId")) {
				queryColumnSql.append("AND ").append(left).append(".deptId = ").append(right).append(".deptId ");
				queryColumnSql.append("AND ").append(left).append(".deptName = ").append(right).append(".deptName ");
			}
			if (queryColumn.contains("investor")) {
				queryColumnSql.append("AND ").append(left).append(".investor = ").append(right).append(".investor ");
				queryColumnSql.append("AND ").append(left).append(".investorName = ").append(right).append(".investorName ");
			}
			if (queryColumn.contains("userGroupId")) {
				queryColumnSql.append("AND ").append(left).append(".userGroupId = ").append(right).append(".userGroupId ");
				queryColumnSql.append("AND ").append(left).append(".userGroupName = ").append(right).append(".userGroupName ");
			}
		}
		sql.append(queryColumnSql);
		return sql;
	}

	private AdDataVo ObjByRow(SqlRowSet rs, List<String> columns) {
		final Optional<List<String>> columnsOpt = Optional.ofNullable(columns);
		AdDataVo vo = new AdDataVo();

		vo.setPeriod(rs.getString("period"));
		vo.setInvestor(columnsOpt.filter(e -> e.contains("investor")).map(e -> rs.getString("investor")).filter(StringUtils::isNotBlank).orElse("-")); // 投放人id
		vo.setInvestorName(columnsOpt.filter(e -> e.contains("investorName")).map(e -> rs.getString("investorName")).filter(StringUtils::isNotBlank).orElse("-")); // 投放人名称
		vo.setDeptId(columnsOpt.filter(e -> e.contains("deptId")).map(e -> rs.getString("deptId")).filter(StringUtils::isNotBlank).orElse("-")); // 部门id
		vo.setDeptName(columnsOpt.filter(e -> e.contains("deptName")).map(e -> rs.getString("deptName")).filter(StringUtils::isNotBlank).orElse("-")); // 部门名称
		vo.setUserGroupId(columnsOpt.filter(e -> e.contains("userGroupId")).map(e -> rs.getString("userGroupId")).filter(StringUtils::isNotBlank).orElse("-")); // 组别id
		vo.setUserGroupName(columnsOpt.filter(e -> e.contains("userGroupName")).map(e -> rs.getString("userGroupName")).filter(StringUtils::isNotBlank).orElse("-")); // 组别名称
		vo.setParentchl(columnsOpt.filter(e -> e.contains("parentchl")).map(e -> rs.getString("parentchl")).filter(StringUtils::isNotBlank).orElse("-")); // 主渠道
		vo.setPgid(columnsOpt.filter(e -> e.contains("pgid")).map(e -> rs.getLong("pgid")).orElse(null)); // 父游戏id
		vo.setOs(columnsOpt.filter(e -> e.contains("os")).map(e -> rs.getInt("os")).orElse(null)); // 系统

		final BigDecimal rudeCost = rs.getBigDecimal("rudeCost");
		vo.setRudeCost(BigDecimal.ZERO.compareTo(rudeCost) == 0 ? BigDecimal.ZERO : rudeCost);
		final BigDecimal cost = rs.getBigDecimal("cost");
		vo.setCost(BigDecimal.ZERO.compareTo(cost) == 0 ? BigDecimal.ZERO : cost);
		vo.setUsrnamenums(rs.getInt("usrnamenums")); // 新增注册设备数
		vo.setCreateRoleCount(rs.getInt("createRoleCount")); // 新增注册创角数
		vo.setCertifiedCount(rs.getInt("certifiedCount")); // 新增注册实名数
		vo.setNotCertifiedCount(rs.getInt("notCertifiedCount")); // 注册未实名数
//		vo.setYoungCount(rs.getInt("youngCount")); // 未成年人数
		vo.setCreateRoleRate(rs.getBigDecimal("createRoleRate")); // 新增创角率
		vo.setCertifiedRate(rs.getBigDecimal("certifiedRate")); // 新增实名制转化率
		vo.setNewPayCount(rs.getInt("newPayCount")); // 新增付费设备数
		vo.setNewPayAmount(rs.getBigDecimal("newPayAmount")); // 新增充值实付金额
		vo.setNewPayGivemoney(rs.getBigDecimal("newPayGivemoney")); // 新增充值代金券金额
		vo.setWeektotalfee(rs.getBigDecimal("weektotalfee")); // 当周充值实付金额
		vo.setWeekPayGivemoney(rs.getBigDecimal("weekPayGivemoney")); // 当周充值代金券金额
		vo.setMonthtotalfee(rs.getBigDecimal("monthtotalfee")); // 当月充值实付金额
		vo.setMonthPayGivemoney(rs.getBigDecimal("monthPayGivemoney")); // 当月充值代金券金额
		vo.setPeriodPayCount(rs.getInt("periodPayCount")); // 期内付费设备数
		vo.setPeriodPayFee(rs.getBigDecimal("periodPayFee")); // 期内充值实付金额
		vo.setPeriodPayGivemoney(rs.getBigDecimal("periodPayGivemoney")); // 期内充值代金券金额
		vo.setTotalPayfee(rs.getBigDecimal("totalPayfee")); // 累计充值实付金额
		vo.setTotalPayGivemoney(rs.getBigDecimal("totalPayGivemoney")); // 累计充值代金券金额
		vo.setActivedevices(rs.getInt("activedevices")); // 活跃设备数
		vo.setActivePayCount(rs.getInt("activePayCount")); // 活跃付费设备数
		vo.setActivetotalfee(rs.getBigDecimal("activetotalfee")); // 活跃充值实付金额
		vo.setActivePayGivemoney(rs.getBigDecimal("activePayGivemoney")); // 活跃充值代金券金额
		vo.setDeviceCose(rs.getBigDecimal("deviceCose")); // 设备成本
		vo.setRegPayRatio(rs.getBigDecimal("regPayRatio")); // 新增付费率
		vo.setPeriodPayRate(rs.getBigDecimal("periodPayRate")); // 期内付费率
		vo.setActivePayRate(rs.getBigDecimal("activePayRate")); // 活跃付费率
		vo.setRegarpu(rs.getBigDecimal("regarpu")); // 新增注册ARPU
		vo.setNewPayArppu(rs.getBigDecimal("newPayArppu")); // 新增付费ARPPU
		vo.setActarpu(rs.getBigDecimal("actarpu")); // 活跃设备ARPU
		vo.setActivePayArppu(rs.getBigDecimal("activePayArppu")); // 活跃付费ARPPU
		vo.setRoi1(rs.getBigDecimal("roi1")); // 首日ROI，新增充值金额/返点后消耗
		vo.setWeekRoi(rs.getBigDecimal("weekRoi")); // 当周ROI，当周充值金额/返点后消耗
		vo.setMonthRoi(rs.getBigDecimal("monthRoi")); // 当月ROI，当月充值金额/返点后消耗
		vo.setAllRoi(rs.getBigDecimal("allRoi")); // 累计充值ROI，累计充值金额/返点后消耗 allRoi
		vo.setRetention2Ratio(rs.getBigDecimal("retention2Ratio")); // 次留
		return vo;
	}

}
